package com.ucom;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

public class Query12
{	public class Tuple12 
	{	private String user_id;
		private int dept_id;

		public Tuple12(String user_id, int dept_id)
		{	super();
			this.user_id = user_id;
			this.dept_id = dept_id;
		}
	}

	private Connection dbConnection;
	private String query;
	private ResultSet rslt;
	private Statement stmtGetListEntries;

	public Query12 ()
	{	try 
		{	Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection("jdbc:derby://localhost:1527/ucomm");
			stmtGetListEntries = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}

	public LinkedList<Tuple12> query12() 
	{	LinkedList<Tuple12> result = new LinkedList<Tuple12>();
		try 
		{		query = "SELECT USER_INFO.USER_ID, DEPT_INFO.DEPT_ID"
						+" FROM DEPT_INFO,USER_INFO, RACE_INFO"
						+" WHERE USER_INFO.DEPT_ID=DEPT_INFO.DEPT_ID"
						+" AND RACE_INFO.RACE_CODE= USER_INFO.RACE"
						+" AND DEPT_NAME='Math'"
						+" AND RACE_INFO.RACE= 'White'";

				rslt = stmtGetListEntries.executeQuery(query);
				System.out.print("--------Query 12 result--------------------\n");
				while (rslt.next()) 
				{	System.out.print(rslt.getString(1));
					System.out.println("\t" + rslt.getInt(2));
					result.add(new Tuple12(rslt.getString(1), rslt.getInt(2)));
				}
				rslt.close();
		} 
		catch (SQLException sqle) 
		{	System.out.println("Error in getting transaction details");
			System.out.println(sqle.getMessage());
		} 
		catch (Exception e) 
		{	e.printStackTrace();
		}
		return result;
	}

}
